﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using DataAccess.DataContract;
using AnTeWei;
using Com.Alipay;

namespace DataAccess
{
    public class DALTransaction
    {
        /// <summary>
        /// 使用传入的数据库连接信息查询数据
        /// </summary>
        /// <param name="dbi"></param>
        /// <param name="strSPName"></param>
        /// <param name="dictParameter"></param>
        /// <param name="dcError"></param>
        /// <returns></returns>
        public DataTable GetDataTableBySPName(string strSPName, Dictionary<string, object> dictParameter)
        {
            DC_Error dcError = new DC_Error();
            SqlConnection sqlConn = null;
            DataTable dt = new DataTable();
            try
            {
                sqlConn = new SqlConnection(GetSQLConnection());

                int Rtn = SQLServer.ExecuteDataTableQueryProcedure("dbo", strSPName, ref dictParameter, sqlConn, sqlConn, ref dt);
                if (Rtn != 0)
                {
                    dcError.ErrorCode = "-1";
                    dcError.ErrorDesc = string.Format("SP {0} fail", strSPName);
                }
            }
            catch (Exception ex)
            {
                Log.Debug("MooseSp:"+strSPName, ex.Message.ToString());
                dcError.ErrorCode = "-1";
                dcError.ErrorDesc = ex.Message;
            }
            finally
            {
                sqlConn = null;
            }

            return dt;
        }

        public DataSet GetDataSetBySPName(string strSPName, Dictionary<string, object> dictParameter)
        {
            DC_Error dcError = new DC_Error();
            SqlConnection sqlConn = null;
            DataSet ds = new DataSet();
            try
            {
                sqlConn = new SqlConnection(GetSQLConnection());

                int Rtn = SQLServer.ExecuteDataSetQueryProcedure("dbo", strSPName, ref dictParameter, sqlConn, sqlConn, ref ds);
                if (Rtn != 0)
                {
                    dcError.ErrorCode = "-1";
                    dcError.ErrorDesc = string.Format("SP {0} fail", strSPName);
                }
            }
            catch (Exception ex)
            {
                dcError.ErrorCode = "-1";
                dcError.ErrorDesc = ex.Message;
            }
            finally
            {
                sqlConn = null;
            }

            return ds;
        }

        public DataTable GetDataTableBySQLText(string strSQL)
        {
            try
            {
                return SQLServer.ExecuteDataTableQuerySQLText(strSQL, GetSQLConnection());
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public bool ExecuteTransactionSP(string strSPName, Dictionary<string, object> dictParameter, ref DC_Error error, ref int iAffectedRow)
        {
            SqlConnection sqlConn = null;
            try
            {
                sqlConn = sqlConn = new SqlConnection(GetSQLConnection());
                iAffectedRow = SQLServer.ExecuteNonQueryProcedure("dbo", strSPName, ref dictParameter, sqlConn, sqlConn);

                if (iAffectedRow == 0)
                    return true;
            }
            catch (Exception ex)
            {
                error.ErrorCode = "-2";
                error.ErrorDesc = ex.Message;
            }
            finally
            {
                sqlConn = null;
                dictParameter = null;
            }

            return false;
        }

        /// <summary>
        /// 无返回参数执行sql语句
        /// </summary>
        /// <param name="strSQL"></param>
        public int ExecuteQuerySQLText(string strSQL)
        {
            SqlConnection sqlConn = sqlConn = new SqlConnection(GetSQLConnection());
            int intCount = -1;
            try
            {
                sqlConn.Open();
                SqlCommand cmd = new SqlCommand(strSQL, sqlConn);
                intCount=cmd.ExecuteNonQuery();
                sqlConn.Close();
                return intCount;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 事务执行sql语句
        /// </summary>
        /// <param name="LsSql">所有需要执行的sql语句</param>
        /// <returns></returns>
        public int ExecuteSqlTrans(List<string> LsSql)
        {
            int intCount = -1;
            //建立连接并打开  
            SqlConnection myConn = new SqlConnection(GetSQLConnection());
            myConn.Open();
            SqlCommand myComm = new SqlCommand(); 
            //注意，SqlTransaction类无公开的构造函数  
            SqlTransaction myTran;
            //创建一个事务  
            myTran = myConn.BeginTransaction();
            try
            {
                //从此开始，基于该连接的数据操作都被认为是事务的一部分  
                //下面绑定连接和事务对象  
                myComm.Connection = myConn;
                myComm.Transaction = myTran; //定位到pubs数据库  
                //利用sqlcommand进行数据操作   
                foreach (string strSql in LsSql)
                {
                    myComm.CommandText = strSql;
                    intCount = myComm.ExecuteNonQuery();
                }
                //提交事务  
                myTran.Commit();
                return intCount;
            }
            catch (Exception err)
            {
                Log.Debug("Moose2", err.Message.ToString());
                DBLog.WriteDBLog("AshxPortalRequest - DemandInformation", err.Message.ToString(), "发布需求失败");
                new ApplicationException("事务操作出错，系统信息：" + err.Message);
                myTran.Rollback();
                return -1;
            }
            finally
            {
                myConn.Close();
            } 
            //int intCount = -1;
            ////初始化连接
            //SqlConnection sqlConn = new SqlConnection(GetSQLConnection());
            //sqlConn.Open();
            ////开启事务   
            //SqlTransaction sqlTransaction = sqlConn.BeginTransaction();

            ////   将事务应用于Command   
            //SqlCommand sqlCommand = new SqlCommand();
            //sqlCommand.Connection = sqlConn;
            //sqlCommand.Transaction = sqlTransaction;
            //try
            //{
                
            //    //利用sqlcommand进行数据操作   
            //    foreach (string strSql in LsSql)
            //    {
            //        sqlCommand.CommandText=strSql;
            //        intCount=sqlCommand.ExecuteNonQuery();
            //    }
                
            //    //   成功提交   
            //    sqlTransaction.Commit();
            //    return intCount;
            //}
            //catch (Exception ex)
            //{
            //    DBLog.WriteDBLog("AshxPortalRequest - DemandInformation", ex.Message.ToString(), "发布需求失败");
            //    //   出错回滚   
            //    sqlTransaction.Rollback();
            //    return -1;
            //}
            //finally
            //{
            //    sqlConn.Close();
            //    sqlTransaction.Dispose();
            //    sqlCommand.Dispose();
            //}   

        }

        private string GetSQLConnection()
        {
            return ConfigurationManager.AppSettings["ConnectionString"];
        }
    }
}
